package com.zlf.web_bjui.servlet.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
	private Connection conn=null;
	public Connection getConnection() throws Exception{
		if(conn==null){
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","201314");
			return conn;
		}else{
			return conn;
		}
	}
	
	/**
	 * 
	 * @param sql
	 */
	public boolean execute(String sql){
		
		try {
			Statement statement = conn.createStatement();
			boolean b =statement.execute(sql);
			return b;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
		
	}
	
	/**
	 * 执行查询语句
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> executeQury(String sql) throws Exception{
		if(conn==null){
			getConnection();
		}
		Statement statement = conn.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		List<Map<String,Object>>  ret=new ArrayList<>();
		List<String> listColumnName=getResultColumn(resultSet);
		while(resultSet.next()){
			Map<String,Object> map=new HashMap<>();
			for(int i=0;i<listColumnName.size();i++){
				String columnName=listColumnName.get(i);
				Object object = resultSet.getObject(columnName);
				map.put(columnName, object);
			}
			ret.add(map);
		}
		
		return ret;
	}
	/**
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	public List<String> getResultColumn(ResultSet resultSet) throws SQLException{
		ResultSetMetaData metaData = resultSet.getMetaData();
		int lineCount=metaData.getColumnCount();
		List<String> listColumnName=new ArrayList<>();
		for(int i=0;i<lineCount;i++){
			String columnName = metaData.getColumnName(i+1);
			listColumnName.add(columnName);
		}
		return listColumnName;
	}
	
	public void close(){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
